﻿CREATE OR REPLACE FUNCTION public.workflowcheck(
    p_bill character varying,
    p_billid integer,
	p_billtype integer,
    p_next_type integer,--1审核通过，2驳回，2取消审核
    p_message text,
    p_userid integer,
    p_billdate character varying,
	p_orgunitid integer,
    p_org_id integer)
  RETURNS json AS
$BODY$
declare  
    v_workflowid integer;  ----工作流程ID
    v_workflow_stateid  integer;  ----工作流程状态ID
    v_next_workflow_stateid  integer=0;  ----下一工作流程状态ID
    v_status integer;---审批后但据状态
    v_workflow_transitionid integer;---工作流程
	v_workflow_transition_tempid integer;---工作流程
	v_workflow_bill_stateid integer;
	v_billuserid integer;
    v_cnt integer;
    v_sql text;
    v_data text;
	v_docstatus integer;
	v_next_docstatus integer;
	v_alertsid  integer;
	v_billcode character varying(255);
begin
    select COALESCE(max(id),0) into v_workflow_bill_stateid 
	From public.workflow_bill_state where org_id=p_org_id and bill_enum=p_bill and billid=p_billid and bill_type=p_billtype and is_active=true;

	if v_workflow_bill_stateid =0 then
	    update public.workflow_bill_state set is_active=false
		where org_id=p_org_id and bill_enum=p_bill and billid=p_billid and bill_type=p_billtype and is_active=true and id<>v_workflow_bill_stateid;

	    select count(id)
	    into v_cnt
	    From workflow where org_id =p_org_id and is_active=true and bill_enum=p_bill  and bill_type=p_billtype
		and (orgunitid=0 or orgunitid=p_orgunitid or orgunitid in(select id From orgunit where position(code in (select code from orgunit where id= p_orgunitid))=1));
	    if v_cnt>1 then--活动审批流有多个
		   select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'活动审批流有多个,审核失败' as message ) t;
		   return v_data::json; 
	    end if;
		select COALESCE(max(id),0)into v_workflowid
        From workflow where org_id =p_org_id and is_active=true and bill_enum=p_bill  and bill_type=p_billtype
		and (orgunitid=0 or orgunitid=p_orgunitid or orgunitid in(select id From orgunit where position(code in (select code from orgunit where id= p_orgunitid))=1));
	    select nextval('workflow_bill_state_id_seq'::regclass)  into v_workflow_bill_stateid ;
		---插入单据状态
		insert into public.workflow_bill_state(id,org_id,userid,docstatus,workflowid,workflow_stateid,billid,bill_enum,bill_type,is_active,message)
		select v_workflow_bill_stateid,p_org_id,p_userid,t1.docstatus,v_workflowid,t1.id,
		p_billid,p_bill,p_billtype,true,'开始审核'
		from public.workflow_state t1 where t1.org_id=p_org_id and t1.docstatus=0;
		if p_bill='employee' then
			v_sql := 'update ' || p_bill || ' set maintainer=' || p_userid ||' where id=' || p_billid;
			execute  v_sql; 
		end if;
	else
	    update public.workflow_bill_state set is_active=false
		where org_id=p_org_id and bill_enum=p_bill and billid=p_billid and bill_type=p_billtype and is_active=true and id<>v_workflow_bill_stateid;
	end if;
	select workflowid into v_workflowid
	From public.workflow_bill_state where id=v_workflow_bill_stateid;
	v_sql := 'select status From ' || p_bill || ' where id=' || p_billid;
	if p_bill='employee' then 
		v_sql :='select case when openseapool =' || p_billtype || ' then status  else 2 end From employee where id=' || p_billid;
	end if;
	raise notice 'Parameter is: %',v_sql;
    execute  v_sql into v_status; ---查找状态
	if v_workflowid=0 then--没有审批流
	     v_sql := 'select userid From ' || p_bill || ' where id=' || p_billid;
      	 execute  v_sql into v_billuserid; ---查找制单人ID
		-------------------------------------检查权限
		if v_billuserid != p_userid then
			SELECT count(users.id) into v_cnt
			FROM users,groups 
			WHERE users.id=p_userid and groups.id = ANY (users.groups) and 'admin'=ANY(groups.permissions);
			if v_cnt=0 then
				delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
				select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限，只有制单人或系统管理员可以审批' as message ) t;
				return v_data::json; 
			end if;
		end if;
		v_sql :='';
		if p_next_type=1 then
		    if v_status=2 then
				delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
				select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'单据已审批！' as message ) t;
				return v_data::json; 
			end if;
			--普通单据
			v_sql := 'update ' || p_bill || ' set status=2,updated_at=now() where id=' || p_billid;
			--人员信息
			if p_bill='employee' then
				if p_billtype=0 then
					v_sql := 'update ' || p_bill || ' set status=0,openseapool=1,updated_at=now() where id=' || p_billid;
				else
					v_sql := 'update ' || p_bill || ' set status=0,openseapool=0,updated_at=now() where id=' || p_billid;
				end if;
			end if;
		else
			if v_status=0 then
				delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
				select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'单据已是草稿状态！' as message ) t;
				return v_data::json; 
			end if;
			--普通单据
			v_sql := 'update ' || p_bill || ' set status=0,updated_at=now() where id=' || p_billid;
			--人员信息
			if p_bill='employee' then
				if p_billtype=0 then
					v_sql := 'update ' || p_bill || ' set status=0,updated_at=now() where id=' || p_billid;
				else
					v_sql := 'update ' || p_bill || ' set status=0,updated_at=now() where id=' || p_billid;
				end if;
			end if;
		end if;
		update public.workflow_bill_state set is_active=false,docstatus=2
		where id = v_workflow_bill_stateid;
		execute  v_sql; 
		select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,'审核成功' as message ) t;
		return v_data::json; 
	end if;

    if p_next_type=1 then
        if v_status=2 then
			delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'单据已审批！' as message ) t;
            return v_data::json; 
        end if;
    elsif p_next_type=3 then--提交撤销
	    if v_status=0 then
			delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'单据已是草稿状态！' as message ) t;
            return v_data::json; 
        end if;
 
		if p_bill='project' then
			select count(*) into v_cnt
			From paymentactivity where projectid=p_billid;
			if v_cnt>0 then
			 	delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
				select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'该项目已有收付款，不能取消审核！' as message ) t;
				return v_data::json; 
			end if;
		end if;
		--raise notice 'Parameter is147: %',v_workflow_bill_stateid;
	    ---20210521添加是否可以	
		select COALESCE(max(completed_by),0) into v_cnt
		from public.workflow_action
		where id=(select max(id)From public.workflow_action where billid=p_billid and bill_enum=p_bill and status=2);

	    if v_cnt != p_userid then
            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'只能对自己审核的单据进行取消审核操作，下一流程已审核或不是自己审核的单据不能操作！' as message ) t;
            return v_data::json; 
	    else
	        v_sql := 'update ' || p_bill || ' set status=0,updated_at=now() where id=' || p_billid;
	        execute  v_sql; 
			insert into public.workflow_action
			(updated_at,userid,workflowid,billid,bill_enum,bill_type,
			workflow_stateid,completed_by,status,org_id,comments,workflow_bill_stateid)
			select p_billdate::timestamp with time zone,p_userid,v_workflowid,p_billid,p_bill,p_billtype,
			0,0,0 as status,p_org_id,'<div>撤销审核</div>' as comments,v_workflow_bill_stateid;
			
	        delete From public.workflow_action where billid=p_billid and bill_enum=p_bill and workflow_bill_stateid = v_workflow_bill_stateid and completed_by=0; 

		   	update  public.workflow_bill_state set is_active=false where id=v_workflow_bill_stateid;
			-----------------------------添加提醒==================
			select nextval('public.alerts_id_seq') into v_alertsid;
			v_sql := 'select code  From ' || p_bill || ' where id=' || p_billid;
    		execute  v_sql into v_billcode;
			
			insert into public.alerts(updated_at,created_at,id,name,user_id,options,state)
	  		select now(),now(),v_alertsid,name || ':' || v_billcode || ' 取消审核',
			p_userid,'{"bill":"' || p_bill || '","billid":' || p_billid || '}','unknown'
			from public.tabletype where code=p_bill and type=p_billtype;
			
			insert into public.alert_subscriptions(updated_at,created_at,user_id,alert_id)
			select now(),now(),t1.completed_by,v_alertsid
			From public.workflow_action t1 inner join public.workflow_state t2 on t1.workflow_stateid=t2.id and t2.docstatus=0
			where t1.workflow_bill_stateid=v_workflow_bill_stateid and t1.status=2 and t1.completed_by<>p_userid;
			-----------------------------end==================
            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,'已撤销审核！' as message ) t;
            return v_data::json; 
	    end if;
    else
        if v_status !=1 then
		    delete from  public.workflow_bill_state where id=v_workflow_bill_stateid;
            select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,
			 case when v_status=0 then '单据已取消审批！' else '单据已审批！' end || '不能驳回' as message ) t;
            return v_data::json; 
        end if;
    end if; 
	select workflow_stateid into v_workflow_stateid
	From public.workflow_bill_state where id=v_workflow_bill_stateid;
	select docstatus into v_docstatus
	From public.workflow_bill_state where id=v_workflow_bill_stateid;
	
	v_sql := 'select COALESCE(min(id),0) from  
	(select public.workflowtabsel(''' || p_bill || ''',condition,''id'',''id'',''' || p_billid || ''') as billid , id 
	from  public.workflow_transition where workflowid=' || v_workflowid || ' and workflow_stateid=' || v_workflow_stateid || ' and next_type= 1) aa
	where aa.billid>0';

	execute  v_sql into v_workflow_transitionid; 
	if v_workflow_transitionid=0 then
		select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'审批流程设置不对，请检查' as message ) t;
		return v_data::json; 
	end if;
	----检查权限
	select count(users.id) into v_cnt from 
	(select allow_user,allow_group from  public.workflow_transition where id in(select id from  
	(select public.workflowtabsel(p_bill,condition,'id','id',p_billid) as billid , id 
	from  public.workflow_transition where workflowid=v_workflowid and workflow_stateid=v_workflow_stateid and next_type= 1) aa
	where aa.billid>0)) obj,users
	where ((users.id = any(obj.allow_user)) 
	or (users.groups && obj.allow_group))
	and users.id=p_userid ;
	if v_cnt =0 then
		select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'没有权限' as message ) t;
		return v_data::json; 
	end if;
	if p_next_type=1 then
		select next_workflow_stateid into v_next_workflow_stateid
		From public.workflow_transition where id=v_workflow_transitionid ;
		v_sql := 'select COALESCE(min(id),0) from  
		(select public.workflowtabsel(''' || p_bill || ''',condition,''id'',''id'',''' || p_billid || ''') as billid , id 
		from  public.workflow_transition where workflowid=' || v_workflowid || ' and workflow_stateid=' || v_next_workflow_stateid || ' and next_type= 1) aa
		where aa.billid>0';
		execute  v_sql into v_workflow_transition_tempid; 
		if v_workflow_transition_tempid>0 then
			v_workflow_transitionid := v_workflow_transition_tempid;
		end if;

	else
		if v_docstatus=0 then
			select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'已经是草稿状态' as message ) t;
			return v_data::json; 
		end if;
		v_sql := 'select COALESCE(min(id),0) from  
		(select public.workflowtabsel(''' || p_bill || ''',condition,''id'',''id'',''' || p_billid || ''') as billid , id 
		from  public.workflow_transition where workflowid=' || v_workflowid || ' and next_workflow_stateid=' || v_workflow_stateid || ' and next_type= 1) aa
		where aa.billid>0';
		execute  v_sql into v_workflow_transitionid;
		if v_workflow_transitionid=0 then
			select array_to_json(array_agg(row_to_json(t))) into v_data from (select 1 as flag,'审批流程设置不对，请检查' as message ) t;
			return v_data::json; 
		end if;
		select workflow_stateid into v_next_workflow_stateid
		From public.workflow_transition where id=v_workflow_transitionid ;
	end if;
	if v_docstatus=0 then
		insert into public.workflow_action
		(updated_at,userid,workflowid,billid,bill_enum,bill_type,
		workflow_stateid,completed_by,status,org_id,comments,workflow_bill_stateid)
		select p_billdate::timestamp with time zone,p_userid,v_workflowid,p_billid,p_bill,p_billtype,
		v_workflow_stateid,0,0 as status,p_org_id,
		case when p_bill='employee' and p_billtype=0 then '转公海池' when p_bill='employee' and p_billtype=1 then '转私有池' else '' end as comments,v_workflow_bill_stateid;
	end if;

	select docstatus into v_next_docstatus From workflow_state where id=v_next_workflow_stateid;

	update  public.workflow_bill_state set workflow_stateid=v_next_workflow_stateid,docstatus=v_next_docstatus,
	is_active=case when v_next_docstatus =2 or v_next_docstatus =0 then false else true end,
	allow_user=t1.allow_user,allow_group=t1.allow_group,allow_col=t1.allow_col  
	From public.workflow_transition t1 where t1.id=v_workflow_transitionid
	and workflow_bill_state.id=v_workflow_bill_stateid;
	v_sql := 'update ' || p_bill || ' set status=' || v_next_docstatus ||',updated_at=now() where id=' || p_billid;
	--人员信息
	if p_bill='employee' and p_next_type=1 and v_next_docstatus = 2 then
		if p_billtype=0  then
			v_sql := 'update ' || p_bill || ' set status=0,openseapool=case when COALESCE(maintainermove,0)=0 then 1 else 0 end,
			maintainer= COALESCE(maintainermove,0),updated_at=now() where id=' || p_billid;
		else
			v_sql := 'update ' || p_bill || ' set status=0,openseapool=0,updated_at=now() where id=' || p_billid;
		end if;
	end if;
	execute  v_sql;
	
	-----------------------------添加提醒==================
	select nextval('public.alerts_id_seq') into v_alertsid;
	v_sql := 'select code  From ' || p_bill || ' where id=' || p_billid;
	execute  v_sql into v_billcode;

	insert into public.alerts(updated_at,created_at,id,name,user_id,options,state)
	select now(),now(),v_alertsid,name || ':' || v_billcode || (case when p_next_type=1 then '已审核' else '已驳回' end),
	p_userid,'{"bill":"' || p_bill || '","billid":' || p_billid || '}','unknown'
	from public.tabletype where code=p_bill and type=p_billtype;

	insert into public.alert_subscriptions(updated_at,created_at,user_id,alert_id)
	select now(),now(),t1.completed_by,v_alertsid
	From public.workflow_action t1 inner join public.workflow_state t2 on t1.workflow_stateid=t2.id and t2.docstatus=0
	where t1.workflow_bill_stateid=v_workflow_bill_stateid and t1.status=2 and t1.completed_by<>p_userid
	union 
	select now(),now(),t2.id,v_alertsid
	From workflow_transition t1,users t2
	where t1.id=v_workflow_transitionid and ((t2.id = any(t1.allow_user)) or (t2.groups && t1.allow_group)) and t2.id<>p_userid;
	-----------------------------end==================
	------------------xiugaizhuangtai
    update  public.workflow_action
    set completed_by=p_userid,status=case when p_next_type=1 then 2 else 1 end,comments=p_message,updated_at=p_billdate::timestamp with time zone
    where workflowid=v_workflowid and bill_enum=p_bill and billid=p_billid and status=0 and workflow_bill_stateid=v_workflow_bill_stateid;
    if v_next_docstatus != 2 then
		insert into public.workflow_action
		(updated_at,userid,workflowid,billid,bill_enum,bill_type,
		workflow_stateid,completed_by,status,org_id,comments,workflow_bill_stateid)
		select p_billdate::timestamp with time zone,p_userid,v_workflowid,p_billid,p_bill,p_billtype,
		v_next_workflow_stateid,0,0 as status,p_org_id,'' as comments,v_workflow_bill_stateid;
    end if;
	select array_to_json(array_agg(row_to_json(t))) into v_data from (select 0 as flag,'审核成功' as message ) t;
	return v_data::json; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;